package com.jiazhong.basic.atm.dao;

import com.jiazhong.basic.atm.bean.Card;
import com.jiazhong.basic.atm.db.DBManager;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class CardDAO {
    private Connection conn;
    private PreparedStatement ps;
    private ResultSet rs;

    @SneakyThrows
    public int save(Card card) {
        conn = DBManager.getConnection();
        String sql = "insert into card values(null,?,md5(?),?,?)";
        ps = conn.prepareStatement(sql);
        ps.setString(1, card.getCardNo());
        ps.setString(2, card.getPassword());
        ps.setDouble(3, card.getMoney());
        ps.setString(4, card.getUsername());
        return ps.executeUpdate();
    }

    @SneakyThrows
    public int update(String cardNo, double money) {
        conn = DBManager.getConnection();
        String sql = "update card set money=money+? where cardNo=?";
        ps = conn.prepareStatement(sql);
        ps.setDouble(1, money);
        ps.setString(2, cardNo);
        return ps.executeUpdate();
    }

    @SneakyThrows
    public Card login(String cardNo, String password) {
        // conn = DBManager.getConnection();
        conn = DBManager.getDruidConnection();
        String sql = "select * from card where password=md5(?) and cardno=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, password);
        ps.setString(2, cardNo);
        rs = ps.executeQuery();
        if (rs.next()) {
            Card card = new Card();
            card.setId(rs.getInt(1));
            card.setCardNo(cardNo);
            card.setMoney(rs.getDouble(4));
            card.setUsername(rs.getString(5));
            return card;
        }
        return null;
    }

    // 会出现SQL注入问题 select * from card where password=md5('sdsds') and cardno='12112'or'1'='1'
    @SneakyThrows
    public boolean login2(String cardNo, String password) {
        conn = DBManager.getConnection();
        String sql = "select * from card where password=md5('"
                + password + "') and cardno='" + cardNo + "'";
        System.out.println(sql);
        ps = conn.prepareStatement(sql);
        rs = ps.executeQuery();
        return rs.next();
    }

    @SneakyThrows
    public void blockCard(String cardNo) {
        conn = DBManager.getConnection();
        String sql = "update card set blockDate=CURRENT_DATE,errorCount=0 where cardno=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, cardNo);
        ps.executeUpdate();
    }

    @SneakyThrows
    public void errorCount(String cardNo) {
        conn = DBManager.getConnection();
        String sql = "update card set errorCount=errorCount+1 where cardno=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, cardNo);
        ps.executeUpdate();
    }

    @SneakyThrows
    public int getErrorCount(String cardNo) {
        conn = DBManager.getConnection();
        String sql = "select errorCount from card where cardno=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, cardNo);
        rs = ps.executeQuery();
        if (rs.next()) {
            return rs.getInt(1);
        }
        return 0;
    }

    @SneakyThrows
    public boolean isBlock(String cardNo) {
        conn = DBManager.getConnection();
        String sql = "select * from card where cardno=? and blockDate=CURRENT_DATE";
        ps = conn.prepareStatement(sql);
        ps.setString(1, cardNo);
        rs = ps.executeQuery();
        return rs.next(); // true:锁定
    }

    // 查看余额是否充足
    @SneakyThrows
    public boolean isTheBalanceSufficient(String cardNo, double money) {
        conn = DBManager.getConnection();
        String sql = "select money-? from card where cardno=?";
        ps = conn.prepareStatement(sql);
        ps.setDouble(1, money);
        ps.setString(2, cardNo);
        rs = ps.executeQuery();
        if (rs.next()) {
            double result = rs.getDouble(1);
            return result > 0;
        }
        return false;
    }

    @SneakyThrows
    public boolean isExists(String cardNo) {
        conn = DBManager.getConnection();
        String sql = "select count(0) from card where cardno=?";
        ps = conn.prepareStatement(sql);
        ps.setString(1, cardNo);
        rs = ps.executeQuery();
        if (rs.next()) {
            return rs.getInt(1) > 0;
        }
        return false;
    }
}
